package com.ctshk.rpc.tour.mapper;

import java.util.List;

import com.ctshk.rpc.tour.dto.ScheduleDetails;
import com.ctshk.rpc.tour.dto.schedule.TripScheduleGuideDTO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ctshk.common.service.mapper.CtsBaseMapper;
import com.ctshk.rpc.tour.dto.schedule.TripScheduleDepartureDTO;
import com.ctshk.rpc.tour.dto.schedule.TripScheduleListDTO;
import com.ctshk.rpc.tour.entity.SeriesTrip;
import com.ctshk.rpc.tour.entity.SeriesTripSchedule;
import com.ctshk.rpc.tour.mapper.provider.SeriesTripScheduleDepartureProvider;
import com.ctshk.rpc.tour.mapper.provider.SeriesTripScheduleProvider;
import com.ctshk.rpc.tour.req.schedule.ScheduleDepartureListQueryReq;
import com.ctshk.rpc.tour.req.schedule.TripScheduleListQueryReq;

/**
 * <p>
 * 旅行团团期 Mapper 接口
 * </p>
 *
 * @author 谢诗宏
 * @since 2021-01-20
 */
public interface SeriesTripScheduleMapper extends CtsBaseMapper<SeriesTripSchedule> {


    SeriesTripSchedule SeriesTripSchedule(Long id);

    @Select("SELECT * FROM t_series_trip_schedule WHERE id = 18006035247661056")
    SeriesTripSchedule getGroupStage();


    @Select({
            "<script>",
            "select",
            "sts.id,sts.trip_number, sts.departure_date, sts.schedule_status,sts.trip_id,st.title,ststr.clear_seats_time,ststr.clear_seats_unit,ststr.stop_sale_days,ststr.stop_sale_hours,ststr.min_deposit,ststr.delivery_days",
            "from t_series_trip_schedule sts LEFT JOIN  t_series_trip st ON (sts.trip_id =st.id) LEFT JOIN t_series_trip_schedule_team_rule  ststr on (ststr.schedule_id =sts.id)",
            "where sts.id in",
            "<foreach collection='idList' item='id' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    List<TripScheduleListDTO> getTripScheduleList(List<Long> idList);

    /**
     * 查询团期列表
     * @param page
     * @param tripScheduleListQueryReq
     * @param isBook 是否为预订列表查询
     * @return
     */
    @SelectProvider(type = SeriesTripScheduleProvider.class, method = "scheduleList")
    IPage<SeriesTripSchedule> scheduleList(IPage<SeriesTripSchedule> page, TripScheduleListQueryReq tripScheduleListQueryReq, boolean isBook);

    @SelectProvider(type = SeriesTripScheduleDepartureProvider.class, method = "scheduleDepartureList")
    List<TripScheduleDepartureDTO> scheduleDepartureList(@Param("scheduleDepartureListQueryReq") ScheduleDepartureListQueryReq scheduleDepartureListQueryReq);

    @Select({
            "SELECT trip_number,group_type_name , gmt_modified  ",
            "FROM t_series_trip_schedule ",
            "LIMIT 0,10",
    })
    List<SeriesTripSchedule> getSeriesTripScheduleList();

    @Select({
            "SELECT id,title ,cover_photo_url ,tour_type_id ,tour_type_name",
            "FROM t_series_trip ",
            "WHERE tour_type_id IS NOT NULL",
            "GROUP BY tour_type_id,id"
    })
    List<SeriesTrip> getSeriesTripList();


    /**
     * 根据旅团状态和天数获取指定行程的成团信息
     *
     * @return
     */
    @Select({
            "<script>",
            "SELECT id,trip_id",
            "FROM t_series_trip_schedule ",
            "WHERE schedule_status = #{status} ",
            "AND DATEDIFF(departure_date,NOW()) BETWEEN 0 AND #{days}",
            "AND is_deleted = 0 ",
            "AND trip_id IN ",
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    List<Long> getTripScheduleByStatusAndDay(@Param("status") Integer status, @Param("days") Integer days, @Param("ids") List<Long> ids);

    @Select({
            "SELECT id,trip_id,gmt_create FROM t_series_trip_schedule ",
            "WHERE trip_id  = #{id} AND gmt_create >= NOW()",
            "ORDER BY gmt_create ",
    })
    List<Long> getTripScheduleByCreateTime(@Param("id") Long id);

    @Select({"SELECT sts.id,trip_number,schedule_status,departure_date,group_customer_price AS price,",
            "hasFull FROM t_series_trip_schedule sts ",
            ", t_series_trip_schedule_default_cost tsdc ",
            ", (SELECT schedule_id,(CASE ",
            "WHEN (max_people - min_people) > 0 THEN '已满' ",
            "ELSE '' END ",
            ") AS hasFull  FROM t_series_trip_schedule_team_rule ",
            "WHERE trip_id = #{id} AND schedule_id IN ( ",
            "SELECT id FROM t_series_trip_schedule ",
            "WHERE trip_id = #{id} AND (schedule_status = 1 OR schedule_status = 2) ",
            ")) a ",
            "WHERE sts.trip_id = #{id} AND (schedule_status = 1 OR schedule_status = 2) ",
            "AND sts.trip_id = tsdc.trip_id AND a.schedule_id = sts.id and tsdc.is_deleted = 0 ",
            "GROUP BY sts.id"
    })
    List<ScheduleDetails> getTripScheduByTripId(@Param("id") Long id);

    @Select({
            "SELECT (max_people-locked_tourist_num) num ",
            "FROM t_series_trip_schedule a,t_series_trip_schedule_team_rule b ",
            "WHERE a.id = b.schedule_id AND a.id = #{id}"
    })
    Integer getSchedulePositionNumById(@Param("id") Long id);

    /**
     * 导领中心团期列表
     * @param idList
     * @return
     */
    @Select("<script>"+"SELECT a.id,a.schedule_status AS groupStatus,a.travel_days,b.guide_requirement,b.need_trip_consumption,a.collection_place_json AS collectionPlace,b.max_people AS totalPeople\n" +
            "FROM t_series_trip_schedule a\n" +
            "LEFT JOIN t_series_trip_team_rule b ON a.trip_id = b.trip_id\n" +
            "WHERE a.is_deleted = 0 AND a.id IN "+
            "<foreach collection=\"list\" item=\"id\" index=\"index\" open=\"(\" close=\")\" separator=\",\">\n" +
            "#{id}\n" +
            "</foreach>"+
            "</script>")
    List<TripScheduleGuideDTO> getScheduleList(@Param("list") List<Long> idList);

    @Select("SELECT (tr.max_people - ts.locked_tourist_num) remain \n" +
            "FROM  t_series_trip_schedule ts , t_series_trip_schedule_team_rule tr\n" +
            "WHERE ts.id = tr.schedule_id and ts.is_deleted = 0 and ts.id = #{id}")
    Integer queryScheduleRemain(@Param("id") Long id);

    /**
     * 根据团号获取团期ID
     * @param tripNumber
     * @return
     */
    @Select("SELECT id\n" +
            "FROM t_series_trip_schedule\n" +
            "WHERE trip_number = #{trip_number} AND is_deleted = 0 LIMIT 1")
    Long queryIdByNumber(@Param("trip_number") String tripNumber);
}
